﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Drawing;

namespace FmcWebsite.Core
{
    public class Article
    {
        private int id = -1;
        private int author_id = -1;
        private int category_id = -1;
        private string title;
        private string content;
        private DateTime insertdate = DateTime.Now;
        private bool publish = false;
        private List<Attachment> attachments;

        public Article()
        {
        }

        private Article(DataRow art_row)
        {
            Read(art_row);
        }

        private void Read(DataRow art_row)
        {
            id = art_row.Field<int>("article_id");
            author_id = DBConnect.TryGetId(art_row, "aut_author_id");
            category_id = DBConnect.TryGetId(art_row, "cat_category_id");
            title = art_row.Field<string>("title");
            content = art_row.Field<string>("content");
            insertdate = art_row.Field<DateTime>("insertdate");
            publish = art_row.Field<bool>("publish");
        }

        public int Id
        {
            get { return id; }
        }

        public string Title
        {
            get { return title; }
            set { title = value; }
        }

        public string Content
        {
            get { return content; }
            set { content = value; }
        }

        public DateTime InsertDate
        {
            get { return insertdate; }
            set { insertdate = value; }
        }

        public Category Category
        {
            get
            {
                if (category_id != -1)
                    return Category.ById(category_id);
                else
                    return Category.Empty();
            }
            set
            {
                category_id = value.Id;
            }
        }

        public Author Author
        {
            get
            {
                if (author_id == -1)
                    return new Author();
                else
                    return Author.ById(author_id);
            }

            set
            {
                author_id = value.Id;
            }
        }

        public bool Publish
        {
            get { return publish; }
            set { publish = value; }
        }

        public void Insert()
        {
            if (id == -1)
            {
                insertdate = DateTime.Now;
                string cmd = "INSERT INTO article (aut_author_id, cat_category_id, title, content, insertdate, publish) VALUES " +
                    "(@autid, @catid, @title, @content, @insertdate, @publish)";
                if (DBConnect.Database.ExecDDL(cmd, CreateParameters()))
                {
                    // get the insert is successful, retrieve the article id
                    var tbl = DBConnect.Database.Select("SELECT Max(article_id) FROM article");
                    id = tbl.Rows[0].Field<int>(0);
                }
            }
            else throw new Exception("Cannot 'insert' an existing article.");
        }        

        private IEnumerable<Attachment> Attachments
        {
            get
            {
                if (attachments == null)
                {
                    attachments = Attachment.ByArticle(this).ToList();
                    foreach(Attachment a in attachments)
                        a.DeleteNotification += new AttachmentEvent(OnAttachmentDeleteNotification);
                }
                return attachments;
            }
        }

        private void OnAttachmentDeleteNotification(Attachment attachment)
        {
            attachments.Remove(attachment);
        }

        public IEnumerable<AttachmentIMAGE> Images
        {
            get
            {
                return Attachments.Where(x => x is AttachmentIMAGE).Select(x => (AttachmentIMAGE)x);
            }
        }

        public AttachmentAUDIO Audio
        {
            get
            {
                var search = Attachments.Where(x => x is AttachmentAUDIO).Select(x => (AttachmentAUDIO)x);
                if (search.Count() > 0)
                {
                    return search.First();
                }
                return null;
            }
        }

        public AttachmentIMAGE AddImage(string filename, string name)
        {
            Attachment img = Attachment.Insert(this, name, filename);
            attachments.Add(img);
            img.DeleteNotification += new AttachmentEvent(OnAttachmentDeleteNotification);
            return (AttachmentIMAGE)img;
        }

        public AttachmentIMAGE AddImage(string filename, string name, Size crop)
        {
            if (crop.IsEmpty)
                return AddImage(filename, name);

            string tmpFile = @"C:\temp\" + (new FileInfo(filename)).Name + ".tmp.jpg";
            Bitmap bmp = Thumbnailer.CreateThumbnail(Image.FromFile(filename), crop);
            bmp.Save(tmpFile, System.Drawing.Imaging.ImageFormat.Jpeg);
            return AddImage(tmpFile, name);
        }

        private IEnumerable<DbParameter> CreateParameters()
        {
            return new DbParameter[] 
            { 
                DBConnect.Database.CreateParameter("@autid", author_id),                
                DBConnect.Database.CreateParameter("@catid", category_id),                
                DBConnect.Database.CreateParameter("@title", title),
                DBConnect.Database.CreateParameter("@content", content),
                DBConnect.Database.CreateParameter("@insertdate", insertdate),
                DBConnect.Database.CreateParameter("@publish", publish),

            };         
        }

        public void Update()
        {
            if (id != -1)
            {                
                string cmd = "UPDATE article SET aut_author_id=@autid, cat_category_id=@catid, " +
                    "title=@title, content=@content, insertdate=@insertdate, publish=@publish WHERE article_id = " + id.ToString();
                DBConnect.Database.ExecDDL(cmd, CreateParameters());
            }
            else throw new Exception("Cannot 'update' an newly created article.");
        }

        public void Refresh()
        {
            if (id > 0)
            {
                var tbl = DBConnect.Database.Select("SELECT * FROM article WHERE article_id=" + id.ToString());
                Read(tbl.Rows[0]);
            }
        }

        public double SearchHit(string[] keywords)
        {
            // +3 --> title counts +2, content counts +1
            double total = keywords.Count() + 3;
            double score = 0;

            foreach (string key in keywords.Select(x => x.ToUpper()))
            {
                if (title.ToUpper().Contains(key))
                {
                    score += 3;
                    break;
                }

                if (content.ToUpper().Contains(key))
                {
                    score += 2;
                    break;
                }

                if ((Author.Name.ToUpper().Contains(key)) ||
                    Category.Name.ToUpper().Contains(key) ||
                    (insertdate.ToLongDateString().ToUpper().Contains(key)) ||
                    (insertdate.ToLongTimeString().ToUpper().Contains(key)))                    
                {
                    score += 1;                    
                }
            }

            return score / total;
        }

        #region Retrieval

        static public IEnumerable<Article> GetAll()
        {
            var table = DBConnect.Database.Select("SELECT * FROM article");
            foreach (DataRow row in table.Rows)
            {
                yield return new Article(row);
            }            
        }

        static public Article ById(int art_id)
        {
            var tbl = DBConnect.Database.Select("SELECT * FROM article WHERE article_id = " + art_id.ToString());
            return new Article(tbl.Rows[0]);
        }

        static private IEnumerable<Article> ByCategory(int[] cat_ids)
        {
            // construct category list for in the sql where-clause
            string cat_universe = "";
            foreach (int i in cat_ids)
            {
                cat_universe += cat_universe == "" ? "" : ",";
                cat_universe += i.ToString();
            }
            cat_universe = "(" + cat_universe + ")";

            // retrieve from database
            foreach (DataRow r in DBConnect.Database.Select("SELECT * FROM article WHERE cat_category_id in " + cat_universe).Rows)
            {
                yield return new Article(r);
            }
        }

        static public IEnumerable<Article> ByCategory(string category_code, bool incl_children)
        {
            return ByCategory(Category.ByCode(category_code), incl_children);
        }

        static public IEnumerable<Article> ByCategory(Category category, bool incl_children)
        {
            // if no category defined, just get all of them
            if (category == null)
                return GetAll();

            // construct the category list
            Int32[] cat_ids;
            if (incl_children)
            {
                // select all categories
                cat_ids = Category.GetUniverse(category).Select(x => x.Id).ToArray();
            }
            else
                cat_ids = new Int32[] { category.Id };

            return ByCategory(cat_ids);

        }

        static public Article ByCode(string code)
        {
            var tbl = DBConnect.Database.Select("SELECT * FROM article WHERE UPPER(code)=UPPER('" + code + "')");
            return new Article(tbl.Rows[0]);
        }

        #endregion
    }
}
